<?php
/**
 * OS CRM SYSTEM - An open source application development framework for PHP 4.3.2 or newer
 *
 * Model CLASS - DB F(Frontend) Model - OS Admin
 * 
 * @name      DB F(Frontend) Model
 * @package		OS CRM
 * @category  Class
 * @subcategory  Frontend Model
 * @author		CRM SYSTEM Dev Team
 * @copyright	Copyright (c) 2010 - 2012, CRM SYSTEM
 * @since     Version 1.0
 * @filesource
 * @access    public 
 */

// ------------------------------------------------------------------------

class DB_F_Model extends Model {

  //set db - listing data
  var $e_list_limit     = '50';
  var $e_list_offset    = '0';
  var $e_list_order     = 'asc';
  var $e_list_order_by  = 'os_journals_title';
  var $e_list_id        = 'os_journals_id';
  
  function __construct() {
    // Call the Model constructor
    parent::__construct();
  }

  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get Journal Data
	 *
	 * @descp  this will get the Journal dataset
	 * @access public
	 * @param  integer - journal id
	 * @return void
	*/
  function fr_get_journal_data($id = 0) {
    $journals_data  = array();
		$qry = "SELECT  * 
            FROM ".TBL_JOURNALS."
            WHERE os_journals_id = '".filter_data($id,'cint')."'
            ";			
    //__debug($qry);exit ();
		$journals_data = run_cust_qry($qry,TRUE);
		return $journals_data;
  }
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get Voume Tree
	 *
	 * @descp  this will get the Voume dataset
	 * @access public
	 * @param  integer - journal id
	 * @return void
	*/
  function fr_get_volume_tree($id = 0) {
    $volume_all_data  = array();
		$qry = "SELECT  * 
            FROM ".TBL_VOLUME."
            WHERE os_journals_id = '".$id."'
            ORDER BY os_volume_year DESC, os_volume_no DESC, os_volume_issue_no DESC
            ";
    //__debug($qry);exit;  
		$volume_all_data = run_cust_qry($qry); 
		return $volume_all_data;
  }
  
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get Current Voume
	 *
	 * @descp  this will get the Current Voume dataset
	 * @access public
	 * @param  integer - journal id
   * @param  integer - volume id
	 * @return void
	*/
  function fr_get_cur_volume($id = 0, $vol_id = 0) {
    $volume_all_data  = array();
    $whr = '';
    if(!empty($vol_id)){
      $whr = " AND os_volume_id = '".$vol_id."'";
    }
    
		$qry = "SELECT  * 
            FROM ".TBL_VOLUME."
            WHERE os_journals_id = '".$id."' ". $whr ."              
            ORDER BY os_volume_year DESC, os_volume_no DESC, os_volume_issue_no DESC
            LIMIT 1
            ";
    //__debug($qry);exit;  
		$volume_all_data = run_cust_qry($qry, true); 
		return $volume_all_data;
  }
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get Current articles
	 *
	 * @descp  this will get the Current artciles dataset
	 * @access public
	 * @param  integer - journal id
   * @param  integer - volume id
	 * @return void
	*/
  function fr_get_cur_articles($id = 0, $vol_id = 0) {
   $data = array();
   if(!empty($vol_id)){
      $qry = "SELECT a.os_articles_pages,
              v.os_volume_year, v.os_volume_no, 
              v.os_volume_issue_no, v.os_volume_issue_dt_range
              FROM ".TBL_ARTICLES." a
              JOIN ".TBL_VOLUME." v  
              ON a.os_volume_id = v.os_volume_id
              WHERE a.os_journals_id = '".$id."'
              AND a.os_volume_id = '".$vol_id."' 
              ";		
      //__debug($qry);exit();
      $pages_data = run_cust_qry($qry); 
      $cur_vol_pages = array();
      if($pages_data['num_rows'] > 0){
        foreach($pages_data['list'] as $v){
          if(!empty($v['os_articles_pages'])){
            $cur_vol_pages[] = $v['os_articles_pages'];
          }
          $data['cur_vol_no'] = $v['os_volume_no'].', '.$v['os_volume_year'];
          $data['cur_issue_no'] = $v['os_volume_issue_dt_range'];
        }        
        $data['cur_vol_pages'] = (count($cur_vol_pages) > 0) ? implode(', ', $cur_vol_pages):'-';
      }
    }else{
      $data = FALSE;
    }
		return $data;
  }
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get articles list
	 *
	 * @descp  this will get the artciles list
	 * @access public
	 * @param  integer - journal id
   * @param  integer - volume id
	 * @return void
	*/
  function fr_get_articles_list($id = 0, $vol_id = 0) {
  /*get articles info*/
    $qry = "SELECT  * 
            FROM ".TBL_ARTICLES."
            WHERE os_journals_id = '".$id."'
            AND os_volume_id = '".$vol_id."' 
            ";		
    //__debug($qry);
    $data = array();
		$articles_data = run_cust_qry($qry);    
    $data['articles_data'] 	= $articles_data['list'];
    $data['articles_num_rows'] 	= $articles_data['num_rows'];
    return $data;
  }
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Frontend - Get publishers name
	 *
	 * @descp  this will get the publishers name
	 * @access public
	 * @param  integer - journal id
	 * @return void
	*/
  function fr_get_pub_name($id = 0, $ret_id = FALSE) {
    $str_pub_name = '-';
    
    $qry = "SELECT  p.os_pub_name as pname , p.os_pub_id as pid
            FROM " . TBL_PUB . " p
            JOIN " . TBL_JOR_MAP_PUB . " map ON p.os_pub_id = map.os_pub_id
            WHERE map.os_journals_id = '" . $id . "'            
            ORDER BY p.os_pub_name
            LIMIT 1
            ";
    $pub_data = run_cust_qry($qry);
    $arr_pub_name = array();
    $arr_pub_id = array();
    
    $str_pub_name = '';
    $str_pub_id = '';
    
    if ($pub_data['num_rows'] > 0) {
      foreach ($pub_data['list'] as $v) {
        $arr_pub_name[] = $v['pname'];
        $arr_pub_id[] = $v['pid'];
      }
      $str_pub_name = implode(', ', $arr_pub_name);
      $str_pub_id = implode(', ', $arr_pub_id);
    }
    if($ret_id == FALSE){
      return $str_pub_name;
    }else{
      return $str_pub_id;
    }
  }
  
  // ------------------------------------------------------------------------
  
  /** 
	 * Backend - Get Sel Cat
	 *
	 * @descp  get the selected category data for edit view
	 * @access public
	 * @param  integer - id
	 * @return void
	*/   
  function fr_get_cat_name($id = 0){
    $os_category_id = array();
    $qry = "SELECT c.os_category_id as id, c.os_category_name as `name` 
            FROM " . TBL_CATEGORY . " c
            JOIN " . TBL_JOR_MAP_CAT . " m
            ON m.os_category_id = c.os_category_id 
            WHERE os_journals_id = '" . $id . "' ";
    $set = run_cust_qry($qry, TRUE);
    if($set['num_rows'] > 0){ 
      $os_category_id['id'] = $set['list']['id'];
      $os_category_id['name'] = $set['list']['name'];
    }
    return $os_category_id;
  }
  
  function fr_get_stats_data(){
    $data = array();
    $usr_info = $this->session->userdata('usr_info');	
    $data['exp_date'] = __set_date($usr_info['usr_expiry'], 4);
    /*get fav list*/
    $qry = "SELECT j.os_journals_id, j.os_journals_title, j.os_journals_issn
            FROM ". TBL_MEMBERS_FAV ." f
            JOIN ". TBL_JOURNALS ."  j ON f.os_journals_id 	 = j.os_journals_id 	
            WHERE f.os_mem_id = '" . $usr_info['usr_id'] . "'
            AND j.os_journals_status = '1' 
            ORDER BY os_mem_fav_id desc";    
    $set = run_cust_qry($qry);
    $data['j_fav_list'] = $set['list'];
    
    /*get alert list*/
    $qry = "SELECT j.os_journals_id, j.os_journals_title, j.os_journals_issn
            FROM ". TBL_MEMBERS_ALERTS ." f
            JOIN ". TBL_JOURNALS ."  j ON f.os_journals_id 	 = j.os_journals_id 	
            WHERE f.os_mem_id = '" . $usr_info['usr_id'] . "'
            AND j.os_journals_status = '1'  
            ORDER BY os_mem_alerts_id desc";    
    $set = run_cust_qry($qry);
    $data['j_alert_list'] = $set['list'];
    
    /*get new publication list*/
    $qry = "SELECT os_pub_id as `id`,	os_pub_name as `name`
            FROM ". TBL_PUB ."
            WHERE os_pub_status = '1'  
            ORDER BY os_pub_id desc, os_pub_name
            LIMIT 5
            ";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['new_pub'] = $set['list'];
    
    
    /*get latest journlas*/
    $qry = "SELECT j.os_journals_id as `id`, j.os_journals_title as `name`, j.os_journals_issn as `issn`,
            v.os_volume_issue_dt_range as `range`, v.os_volume_no as `vol_no`  
            FROM ". TBL_JOURNALS ."  j
            JOIN ". TBL_VOLUME."  v 
            ON v.os_journals_id 	 = j.os_journals_id 	
            WHERE 
            j.os_journals_status = '1'  
            ORDER BY j.os_journals_id desc
            LIMIT 6";   
    
    $set = run_cust_qry($qry);
    $data['latest_j'] = $set['list'];
    
    $months_back = 1;    
    $dt_from = mktime( date("H", now()), date("i", now()), date("s", now()), (date("m", now()) - $months_back), date("d", now()), date("Y", now()));
    $dt_to = mktime( date("H", now()), date("i", now()), date("s", now()), date("m", now()), date("d", now()), date("Y", now()));
    $data['dt_from'] = standard_date('DATE_OS_CRM_1',$dt_from);
    $data['dt_to'] = standard_date('DATE_OS_CRM_1',$dt_to);
    
    /*__debug(unix_to_human($dt_from,TRUE,'n'));
    __debug(unix_to_human($dt_to,TRUE,'n'));
    exit();*/
    
    /*get most read ARTICLES*/
    $qry = "SELECT count(rd.os_stats_most_read_id) as cnt, rd.os_articles_id as `id`, 
            art.os_articles_title as `name`, art.os_journals_id as `jid`
            FROM ".TBL_STATS_MOST_RD." rd
            JOIN ".TBL_ARTICLES." art
            ON rd.os_articles_id = art.os_articles_id
            WHERE
            os_stats_most_read_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY rd.os_articles_id
            ORDER by cnt desc
            LIMIT 5
            ";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_read'] = $set['list'];
    
    /*get most read PUBLICATIONS*/
    $qry = "SELECT COUNT(st.os_stats_most_read_pub_id) as cnt, pub.os_pub_id as `pid`, pub.os_pub_name as `name`
            FROM ".TBL_STATS_MOST_RD_PUB." st
            JOIN ".TBL_PUB." pub
            ON st.os_pub_id = pub.os_pub_id  
            WHERE
            os_stats_most_read_pub_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY pid
            ORDER BY cnt desc
            LIMIT 5";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_read_pub'] = $set['list'];
    
    /*get most visited PUBLICATIONS*/
    $qry = "SELECT COUNT(st.os_stats_most_vis_pub_id) as cnt, pub.os_pub_id as `pid`, pub.os_pub_name as `name`
            FROM ".TBL_STATS_MOST_VIS_PUB." st
            JOIN ".TBL_PUB." pub
            ON st.os_pub_id = pub.os_pub_id   
            WHERE
            os_stats_most_vis_pub_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY pid
            ORDER BY cnt desc
            LIMIT 5";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_vis_pub'] = $set['list'];
    return $data;
  }
}
?>
